﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinStudent
{
    public partial class FrmSCList : Form
    {
        public FrmSCList()
        {
            InitializeComponent();
        }

        private void FrmGradeList_Load(object sender, EventArgs e)
        {         
            InitSCList();
            InitSnoList();
            InitCnoList();
        }

        /// <summary>
        /// 初始化学号下拉框
        /// </summary>
        private void InitSnoList()
        {
            string sql = "select Sno from Student order by Sno ";
            DataTable dataTableSno = SqlHelper.GetDataTable(sql);
            //添加一行默认选择
            DataRow datarow = dataTableSno.NewRow();
            datarow["Sno"] = "请选择";
            
            dataTableSno.Rows.InsertAt(datarow, 0);

            cboSno.DataSource = dataTableSno;
            //学号--项
            cboSno.DisplayMember = "Sno";//显示的内容
            cboSno.ValueMember = "Sno";//值

            cboSno.SelectedIndex = 0;
        }

        /// <summary>
        /// 初始化课程号下拉框
        /// </summary>
        private void InitCnoList()
        {
            string sql = "select Cno from Course order by Cno ";
            DataTable dataTableCno = SqlHelper.GetDataTable(sql);
            //添加一行默认选择
            DataRow datarow = dataTableCno.NewRow();
            datarow["Cno"] = "请选择";

            dataTableCno.Rows.InsertAt(datarow, 0);

            cboCno.DataSource = dataTableCno;
            //课程号--项
            cboCno.DisplayMember = "Cno";//显示的内容
            cboCno.ValueMember = "Cno";//值

            cboCno.SelectedIndex = 0;
        }

        private void InitSCList()
        {
            string sql = "select Sno,Cno,Grade from SC where IsDeleted=0 ";
            DataTable dataGradeList = SqlHelper.GetDataTable(sql);
            dgvGradeList.DataSource = dataGradeList;
            FrmStudentList.Init_colEdit_colDelete(dgvGradeList);
        }

        /// <summary>
        /// 查询选课成绩信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFind_Click(object sender, EventArgs e)
        {
            //dgvGradeList.Columns.Remove("colEdit");
            //dgvGradeList.Columns.Remove("colDelete");
            //dgvGradeList.DataSource = null;
            string sno = (string)cboSno.SelectedValue;
            string cno = (string)cboCno.SelectedValue;

            //拼接式sql注入
            //string sql = "select Student.Sno as 学号,Course.Cno as 课程号,Grade as 成绩 from SC join Student on Student.Sno=SC.Sno join Course on Course.Cno=SC.Cno where 1=1 ";
            //if (cboSno.SelectedIndex!=0)
            //{
            //    sql += " and Student.Sno='" + sno + "' ";
            //}
            //if (cboCno.SelectedIndex!=0)
            //{
            //    sql += " and Course.Cno='" + cno + "' ";
            //}
            //DataTable dataGradeList = SqlHelper.GetDataTable(sql);

            //参数化sql数组
            string sql = "select Sno,Cno,Grade from SC where IsDeleted=0 ";
            if (sno != "请选择")
            {
                sql += " and Sno=@Sno ";
            }
            if (cno != "请选择")
            {
                sql += " and Cno=@Cno ";
            }
            SqlParameter[] paras =
            {
                new SqlParameter("@Sno",sno),
                new SqlParameter("@Cno",cno)
            };
            DataTable dataGradeList = SqlHelper.GetDataTable(sql, paras);

            dgvGradeList.DataSource = dataGradeList;
            //FrmStudentList.Init_colEdit_colDelete(dgvGradeList);
        }

        private void dgvGradeList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1)
            {
                DataRow dr = (dgvGradeList.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;

                DataGridViewCell cell = dgvGradeList.Rows[e.RowIndex].Cells[e.ColumnIndex];
                if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "修改")
                {
                    //修改操作
                    string SC = dr["Sno"].ToString().Trim()+ ","+dr["Cno"].ToString().Trim(); ;
                    FrmEditSC frmEditSC = new FrmEditSC();
                    frmEditSC.Tag = SC;
                    frmEditSC.MdiParent = this.MdiParent;
                    frmEditSC.Show();//顶级窗体
                }
                else if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "删除")
                {
                    //删除操作
                    if (MessageBox.Show("确定删除？", "删除课程提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        string sno = dr["Sno"].ToString().Trim();
                        string cno = dr["Cno"].ToString().Trim();
                        //假删除 IsDeleted 0  1
                        string sqlDel = "update SC set IsDeleted=1 where Cno=@Cno and Sno=@Sno ";
                        SqlParameter[] paras =
                        {
                            new SqlParameter("@Sno",sno),
                            new SqlParameter("@Cno",cno)
                        };
                        int count = SqlHelper.ExecutNonQuery(sqlDel, paras);
                        if (count > 0)
                        {
                            MessageBox.Show("删除成功", "删除选课信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            //DataGridView数据并没有刷新，手动刷新
                            DataTable dtGrade = (DataTable)dgvGradeList.DataSource;
                            //dgvCourseList.DataSource = null;
                            dtGrade.Rows.Remove(dr);
                            dgvGradeList.DataSource = dtGrade;
                        }
                        else
                        {
                            MessageBox.Show("删除失败", "删除选课信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        //真删除 delete...where... 
                    }
                }
            }
        }











    }
}
